package com.goscom.app.deeset;

import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import javax.mail.MessagingException;
import com.goscom.app.database.DBAdapter;
import android.content.ContentValues;
import android.content.Context;
import android.content.Intent;
import jxl.Workbook;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

public class ExportExcel {

	private File fileExcel;
	private WritableWorkbook workbook;
	private WritableSheet sheet;
	private WritableFont cellFontLab, cellFontCon;
	private WritableCellFormat cellFormatLab, cellFormatCon, cellFormatDate;
	private Context ctx;
	private String strMerId = "";
	private DBAdapter mDB;
	private Mail mail;

	public ExportExcel(Context ctx, String strMerId, DBAdapter mDB) {
		this.ctx = ctx;
		this.strMerId = strMerId;
		this.mDB = mDB;

		fileExcel = new File(
				"/data/data/com.goscom.app.deeset/merchandiser.xls");

		String strAttchPath = "/data/data/com.goscom.app.deeset/merchandiser.xls";
		String strAttchUpload1 = "/mnt/sdcard/DCMI/" + strMerId
				+ "colleague1.jpg";
		String strAttchUpload2 = "/mnt/sdcard/DCMI/" + strMerId
				+ "colleague2.jpg";
		String strAttchUpload3 = "/mnt/sdcard/DCMI/" + strMerId
				+ "colleague3.jpg";
		String strAttchUpload4 = "/mnt/sdcard/DCMI/" + strMerId
				+ "colleague4.jpg";
		String strAttchSign = "/mnt/sdcard/DCMI/" + strMerId + "signature.png";

		try {
			workbook = Workbook.createWorkbook(fileExcel);
			sheet = workbook.createSheet("Merchandiser " + strMerId, 0);

			setAtrributeStyle();

			insertCells();

			workbook.write();
			workbook.close();

			mail = new Mail("deesethr@deeset.co.uk", "deeset2036", ctx);

			String strRec = "naved.khan@deeset.co.uk";
			/* String strRec = "toantt9999@gmail.com"; */
			String[] arrayToAccount = strRec.split(";");
			mail.setToAccount(arrayToAccount);
			mail.setFromAccount("deesethr@deeset.co.uk");
			mail.setSubject("[DEESET] - Details");
			mail.setBody("View file attach.");

			File fileUpload1 = new File(strAttchUpload1);
			if (fileUpload1.exists()) {
				try {
					mail.attachFile(strAttchUpload1, "deeset/colleague1.jpg");
				} catch (MessagingException e) {
					e.printStackTrace();
				}
			}
			File fileUpload2 = new File(strAttchUpload2);
			if (fileUpload2.exists()) {
				try {
					mail.attachFile(strAttchUpload2, "deeset/colleague2.jpg");
				} catch (MessagingException e) {
					e.printStackTrace();
				}
			}
			File fileUpload3 = new File(strAttchUpload3);
			if (fileUpload3.exists()) {
				try {
					mail.attachFile(strAttchUpload3, "deeset/colleague3.jpg");
				} catch (MessagingException e) {
					e.printStackTrace();
				}
			}
			File fileUpload4 = new File(strAttchUpload4);
			if (fileUpload4.exists()) {
				try {
					mail.attachFile(strAttchUpload4, "deeset/colleague4.jpg");
				} catch (MessagingException e) {
					e.printStackTrace();
				}
			}
			File fileSign = new File(strAttchSign);
			if (fileSign.exists()) {
				try {
					mail.attachFile(strAttchSign, "deeset/prooft of status.jpg");
				} catch (MessagingException e) {
					e.printStackTrace();
				}
			}
			File fileExecel = new File(strAttchPath);
			if (fileExecel.exists()) {
				try {
					mail.attachFile(strAttchPath, "deeset/interview form.xls");
				} catch (MessagingException e) {
					e.printStackTrace();
				}
			}
			if (mail.send()) {
				ctx.startActivity(new Intent(ctx, Login.class));
			}

		} catch (IOException e) {
			e.printStackTrace();
		} catch (WriteException e) {
			e.printStackTrace();
		}
	}

	public void setAtrributeStyle() {
		try {
			cellFontLab = new WritableFont(WritableFont.ARIAL);
			cellFontLab.setBoldStyle(WritableFont.BOLD);
			cellFormatLab = new WritableCellFormat(cellFontLab);
			cellFontLab.setColour(Colour.WHITE);
			cellFormatLab.setBackground(Colour.GRAY_50);
			cellFormatLab.setBorder(Border.LEFT, BorderLineStyle.THIN,
					Colour.BLACK);
			cellFormatLab.setBorder(Border.TOP, BorderLineStyle.THIN,
					Colour.BLACK);
			cellFormatLab.setBorder(Border.RIGHT, BorderLineStyle.THIN,
					Colour.BLACK);
			cellFormatLab.setBorder(Border.BOTTOM, BorderLineStyle.THIN,
					Colour.BLACK);

			cellFontCon = new WritableFont(WritableFont.ARIAL);
			cellFontCon.setBoldStyle(WritableFont.BOLD);
			cellFormatCon = new WritableCellFormat(cellFontCon);
			cellFormatCon.setBorder(Border.LEFT, BorderLineStyle.THIN,
					Colour.BLACK);
			cellFormatCon.setBorder(Border.TOP, BorderLineStyle.THIN,
					Colour.BLACK);
			cellFormatCon.setBorder(Border.RIGHT, BorderLineStyle.THIN,
					Colour.BLACK);
			cellFormatCon.setBorder(Border.BOTTOM, BorderLineStyle.THIN,
					Colour.BLACK);
		} catch (WriteException e) {
			e.printStackTrace();
		}
	}

	public void insertCells() {
		try {
			ContentValues content = mDB.queryDataByMerId("MERNAME", strMerId);
			sheet.addCell(new Label(0, 0, "FirstName", cellFormatLab));
			sheet.addCell(new Label(1, 0, "MiddleName", cellFormatLab));
			sheet.addCell(new Label(2, 0, "LastName", cellFormatLab));
			sheet.addCell(new Label(3, 0, "Area", cellFormatLab));
			sheet.addCell(new Label(4, 0, "AreaManagerName", cellFormatLab));
			sheet.addCell(new Label(5, 0, "DateOfEmployement", cellFormatLab));
			sheet.addCell(new Label(69, 0, "tlName", cellFormatLab));
			sheet.addCell(new Label(70, 0, "tile", cellFormatLab));
			sheet.addCell(new Label(72, 0, "gender", cellFormatLab));

			sheet.addCell(new Label(0, 1, content.getAsString("forename"),
					cellFormatCon));
			sheet.addCell(new Label(2, 1, content.getAsString("surname"),
					cellFormatCon));
			sheet.addCell(new Label(3, 1, content.getAsString("area"),
					cellFormatCon));
			sheet.addCell(new Label(4, 1, content.getAsString("amname"),
					cellFormatCon));
			sheet.addCell(new Label(5, 1, content.getAsString("date"),
					cellFormatCon));
			sheet.addCell(new Label(69, 1, content.getAsString("tlname"),
					cellFormatCon));
			sheet.addCell(new Label(70, 1, content.getAsString("title"),
					cellFormatCon));
			sheet.addCell(new Label(72, 1, content.getAsString("gender"),
					cellFormatCon));

			content = mDB.queryDataByMerId("MER", strMerId);
			sheet.addCell(new Label(6, 0, "homeTelephoneNumber", cellFormatLab));
			sheet.addCell(new Label(7, 0, "subjectToImmigrationControl",
					cellFormatLab));
			sheet.addCell(new Label(8, 0, "emailAddress", cellFormatLab));
			sheet.addCell(new Label(9, 0, "mobileNumber", cellFormatLab));
			sheet.addCell(new Label(10, 0, "nationality", cellFormatLab));
			sheet.addCell(new Label(11, 0, "dob", cellFormatLab));
			sheet.addCell(new Label(71, 0, "nationalInsuranceNumber",
					cellFormatLab));
			sheet.addCell(new Label(73, 0, "thisIsMyFirstJob", cellFormatLab));
			sheet.addCell(new Label(74, 0, "thisIsNowMyOnlyJob", cellFormatLab));
			sheet.addCell(new Label(75, 0, "ihavAnotherJob", cellFormatLab));
			sheet.addCell(new Label(76, 0, "studentLoans", cellFormatLab));
			sheet.addCell(new Label(77, 0, "dateEmployementStarted",
					cellFormatLab));
			sheet.addCell(new Label(78, 0, "jobTitle", cellFormatLab));

			content = mDB.queryDataByMerId("PERINFO", strMerId);
			if (content.getAsString("userid") == null) {
				sheet.addCell(new Label(6, 1, content.getAsString("telnum"),
						cellFormatCon));
				sheet.addCell(new Label(7, 1, convertBoolean(content
						.getAsString("control")), cellFormatCon));
				sheet.addCell(new Label(8, 1, content.getAsString("email"),
						cellFormatCon));
				sheet.addCell(new Label(9, 1, content.getAsString("mobnum"),
						cellFormatCon));
				sheet.addCell(new Label(10, 1, content.getAsString("nation"),
						cellFormatCon));
				sheet.addCell(new Label(11, 1, content.getAsString("birth"),
						cellFormatCon));
				sheet.addCell(new Label(71, 1, content.getAsString("insur"),
						cellFormatCon));

				sheet.addCell(new Label(73, 1, convertBoolean(content
						.getAsString("ckcA")), cellFormatCon));
				sheet.addCell(new Label(74, 1, convertBoolean(content
						.getAsString("ckcB")), cellFormatCon));
				sheet.addCell(new Label(75, 1, convertBoolean(content
						.getAsString("ckcC")), cellFormatCon));
				sheet.addCell(new Label(76, 1, convertBoolean(content
						.getAsString("ckcD")), cellFormatCon));
				sheet.addCell(new Label(77, 1, content.getAsString("strdate"),
						cellFormatCon));
				sheet.addCell(new Label(78, 1, content.getAsString("jobtit"),
						cellFormatCon));
			}

			content = mDB.queryDataByMerId("MER", strMerId);
			sheet.addCell(new Label(12, 0, "houseNumber", cellFormatLab));
			sheet.addCell(new Label(13, 0, "townCity", cellFormatLab));
			sheet.addCell(new Label(14, 0, "county", cellFormatLab));
			sheet.addCell(new Label(15, 0, "postCode", cellFormatLab));
			content = mDB.queryDataByMerId("ADDRESS", strMerId);
			if (content.getAsString("userid") == null) {
				sheet.addCell(new Label(12, 1,
						content.getAsString("numstreet"), cellFormatCon));
				sheet.addCell(new Label(13, 1, content.getAsString("city"),
						cellFormatCon));
				sheet.addCell(new Label(14, 1, content.getAsString("county"),
						cellFormatCon));
				sheet.addCell(new Label(15, 1, content.getAsString("postcode"),
						cellFormatCon));
			}

			content = mDB.queryDataByMerId("MER", strMerId);
			sheet.addCell(new Label(16, 0, "licencepresent", cellFormatLab));
			sheet.addCell(new Label(17, 0, "noofpoints", cellFormatLab));
			content = mDB.queryDataByMerId("VEHICLE", strMerId);
			if (content.getAsString("userid") == null) {
				sheet.addCell(new Label(16, 1, convertBoolean(content
						.getAsString("licence")), cellFormatCon));
				sheet.addCell(new Label(17, 1, content.getAsString("pointlic"),
						cellFormatCon));
			}

			content = mDB.queryDataByMerId("MER", strMerId);
			sheet.addCell(new Label(18, 0, "refree1Name", cellFormatLab));
			sheet.addCell(new Label(19, 0, "refree1Role", cellFormatLab));
			sheet.addCell(new Label(20, 0, "refree1Tele", cellFormatLab));
			sheet.addCell(new Label(21, 0, "refree1Address", cellFormatLab));
			content = mDB.queryDataByMerAddOther("REFEREE", strMerId,
					"referee1");
			if (content.getAsString("userid") == null) {
				sheet.addCell(new Label(18, 1, content.getAsString("name"),
						cellFormatCon));
				sheet.addCell(new Label(19, 1, content.getAsString("role"),
						cellFormatCon));
				sheet.addCell(new Label(20, 1,
						content.getAsString("telephone"), cellFormatCon));
				sheet.addCell(new Label(21, 1, content.getAsString("conadd"),
						cellFormatCon));
			}

			content = mDB.queryDataByMerId("MER", strMerId);
			sheet.addCell(new Label(22, 0, "refree2Name", cellFormatLab));
			sheet.addCell(new Label(23, 0, "refree2Role", cellFormatLab));
			sheet.addCell(new Label(24, 0, "refree2Tele", cellFormatLab));
			sheet.addCell(new Label(25, 0, "refree2Address", cellFormatLab));
			content = mDB.queryDataByMerAddOther("REFEREE", strMerId,
					"referee2");
			if (content.getAsString("userid") == null) {
				sheet.addCell(new Label(22, 1, content.getAsString("name"),
						cellFormatCon));
				sheet.addCell(new Label(23, 1, content.getAsString("role"),
						cellFormatCon));
				sheet.addCell(new Label(24, 1,
						content.getAsString("telephone"), cellFormatCon));
				sheet.addCell(new Label(25, 1, content.getAsString("conadd"),
						cellFormatCon));
			}

			content = mDB.queryDataByMerId("MER", strMerId);
			sheet.addCell(new Label(26, 0, "personToContactInEmergency",
					cellFormatLab));
			content = mDB.queryDataByMerId("CONEMER", strMerId);
			if (content.getAsString("userid") == null) {
				sheet.addCell(new Label(26, 1,
						content.getAsString("addtelnum"), cellFormatCon));
			}

			content = mDB.queryDataByMerId("MER", strMerId);
			sheet.addCell(new Label(27, 0, "convicted", cellFormatLab));
			sheet.addCell(new Label(28, 0, "detailsOfConviction", cellFormatLab));
			content = mDB.queryDataByMerId("REHABILITATION", strMerId);
			if (content.getAsString("userid") == null) {
				sheet.addCell(new Label(27, 1, convertBoolean(content
						.getAsString("convicted")), cellFormatCon));
				sheet.addCell(new Label(28, 1, content.getAsString("detail"),
						cellFormatCon));
			}

			content = mDB.queryDataByMerId("MER", strMerId);
			sheet.addCell(new Label(29, 0, "Languages", cellFormatLab));
			sheet.addCell(new Label(30, 0, "Qualifications", cellFormatLab));
			sheet.addCell(new Label(31, 0, "Skills", cellFormatLab));
			sheet.addCell(new Label(32, 0, "Hobbies", cellFormatLab));
			content = mDB.queryDataByMerId("ADDITIONAL", strMerId);
			if (content.getAsString("userid") == null) {
				sheet.addCell(new Label(29, 1,
						content.getAsString("languages"), cellFormatCon));
				sheet.addCell(new Label(30, 1, content
						.getAsString("qualifications"), cellFormatCon));
				sheet.addCell(new Label(31, 1, content.getAsString("skills"),
						cellFormatCon));
				sheet.addCell(new Label(32, 1, content.getAsString("hobbies"),
						cellFormatCon));
			}

			content = mDB.queryDataByMerId("MER", strMerId);
			sheet.addCell(new Label(33, 0, "visitorSignInAchieved",
					cellFormatLab));
			content = mDB.queryDataByMerAddOther("STOREINDUCTION", strMerId,
					"storeinduction");
			if (content.getAsString("userid") == null) {
				sheet.addCell(new Label(33, 1, convertBoolean(content
						.getAsString("checkall")), cellFormatCon));
			}

			content = mDB.queryDataByMerId("MER", strMerId);
			sheet.addCell(new Label(34, 0, "deesetMobileAchieved",
					cellFormatLab));
			content = mDB.queryDataByMerAddOther("STOREINDUCTION", strMerId,
					"deesetprocedure");
			if (content.getAsString("userid") == null) {
				sheet.addCell(new Label(34, 1, convertBoolean(content
						.getAsString("checkall")), cellFormatCon));
			}

			content = mDB.queryDataByMerId("MER", strMerId);
			sheet.addCell(new Label(35, 0, "achieved", cellFormatLab));
			sheet.addCell(new Label(36, 0, "electronicTimeAchieved",
					cellFormatLab));
			content = mDB.queryDataByMerAddOther("STOREINDUCTION", strMerId,
					"otherdeeset");
			if (content.getAsString("userid") == null) {
				sheet.addCell(new Label(35, 1, convertBoolean(content
						.getAsString("check1")), cellFormatCon));
				sheet.addCell(new Label(36, 1, convertBoolean(content
						.getAsString("checkall")), cellFormatCon));
			}

			content = mDB.queryDataByMerId("MER", strMerId);
			sheet.addCell(new Label(37, 0, "dressCodeAchieved", cellFormatLab));
			sheet.addCell(new Label(38, 0, "hivisibilityAchieved",
					cellFormatLab));
			sheet.addCell(new Label(39, 0, "storeFireAchieved", cellFormatLab));
			content = mDB.queryDataByMerAddOther("STOREINDUCTION", strMerId,
					"healthandsafety");
			if (content.getAsString("userid") == null) {
				sheet.addCell(new Label(37, 1, convertBoolean(content
						.getAsString("check1")), cellFormatCon));
				sheet.addCell(new Label(38, 1, convertBoolean(content
						.getAsString("check2")), cellFormatCon));
				sheet.addCell(new Label(39, 1, convertBoolean(content
						.getAsString("checkall")), cellFormatCon));
			}

			content = mDB.queryDataByMerId("MER", strMerId);
			sheet.addCell(new Label(40, 0, "manualHandling", cellFormatLab));
			content = mDB.queryDataByMerAddOther("STOREINDUCTION", strMerId,
					"manual");
			if (content.getAsString("userid") == null) {
				sheet.addCell(new Label(40, 1, convertBoolean(content
						.getAsString("checkall")), cellFormatCon));
			}

			content = mDB.queryDataByMerId("MER", strMerId);
			sheet.addCell(new Label(41, 0, "copyOfProof", cellFormatLab));
			sheet.addCell(new Label(42, 0, "name", cellFormatLab));
			sheet.addCell(new Label(43, 0, "role", cellFormatLab));
			sheet.addCell(new Label(44, 0, "telephone", cellFormatLab));
			sheet.addCell(new Label(45, 0, "address", cellFormatLab));

			content = mDB.queryDataByMerId("MER", strMerId);
			sheet.addCell(new Label(46, 0, "commencingDate", cellFormatLab));
			sheet.addCell(new Label(47, 0, "payRate", cellFormatLab));
			sheet.addCell(new Label(48, 0, "totalContractedHours",
					cellFormatLab));
			sheet.addCell(new Label(49, 0, "totalNonContractedHours",
					cellFormatLab));
			sheet.addCell(new Label(50, 0, "additionalStores", cellFormatLab));
			sheet.addCell(new Label(117, 0, "AgreeToWorkMore48", cellFormatLab));
			content = mDB.queryDataByMerId("OFFERLETTER", strMerId);
			if (content.getAsString("userid") == null) {
				sheet.addCell(new Label(46, 1, content.getAsString("comdate"),
						cellFormatCon));
				sheet.addCell(new Label(47, 1, content.getAsString("pay"),
						cellFormatCon));
				sheet.addCell(new Label(48, 1, content
						.getAsString("contracted"), cellFormatCon));
				sheet.addCell(new Label(49, 1, content
						.getAsString("noncontracted"), cellFormatCon));
				sheet.addCell(new Label(50, 1, content.getAsString("addsto"),
						cellFormatCon));
				sheet.addCell(new Label(117, 1, convertBoolean(content
						.getAsString("agree")), cellFormatCon));
			}

			content = mDB.queryDataByMerId("MER", strMerId);
			sheet.addCell(new Label(51, 0, "storeAndRetailer", cellFormatLab));
			sheet.addCell(new Label(52, 0, "Job_Skill", cellFormatLab));
			sheet.addCell(new Label(53, 0, "mon", cellFormatLab));
			sheet.addCell(new Label(54, 0, "tue", cellFormatLab));
			sheet.addCell(new Label(55, 0, "wed", cellFormatLab));
			sheet.addCell(new Label(56, 0, "thu", cellFormatLab));
			sheet.addCell(new Label(57, 0, "fri", cellFormatLab));
			sheet.addCell(new Label(58, 0, "sat", cellFormatLab));
			sheet.addCell(new Label(59, 0, "sun", cellFormatLab));
			ArrayList<ContentValues> arrlst = new ArrayList<ContentValues>();
			arrlst = mDB.queryListByMerContracted("CONTRACTEDHOURS", strMerId,
					"contracted");
			for (int i = 0; i < arrlst.size(); i++) {
				content = arrlst.get(i);
				if (content.getAsString("userid") == null) {
					sheet.addCell(new Label(51, i + 1, content
							.getAsString("storel"), cellFormatCon));
					sheet.addCell(new Label(52, i + 1, content
							.getAsString("jobskill"), cellFormatCon));
					sheet.addCell(new Label(53, i + 1, content
							.getAsString("mon"), cellFormatCon));
					sheet.addCell(new Label(54, i + 1, content
							.getAsString("tue"), cellFormatCon));
					sheet.addCell(new Label(55, i + 1, content
							.getAsString("wed"), cellFormatCon));
					sheet.addCell(new Label(56, i + 1, content
							.getAsString("thu"), cellFormatCon));
					sheet.addCell(new Label(57, i + 1, content
							.getAsString("fri"), cellFormatCon));
					sheet.addCell(new Label(58, i + 1, content
							.getAsString("sat"), cellFormatCon));
					sheet.addCell(new Label(59, i + 1, content
							.getAsString("sun"), cellFormatCon));
				}
			}

			content = mDB.queryDataByMerId("MER", strMerId);
			sheet.addCell(new Label(60, 0, "storesNonContract", cellFormatLab));
			sheet.addCell(new Label(61, 0, "jobSkillsNonContract",
					cellFormatLab));
			sheet.addCell(new Label(62, 0, "monNonContract", cellFormatLab));
			sheet.addCell(new Label(63, 0, "tueNonContract", cellFormatLab));
			sheet.addCell(new Label(64, 0, "wedNonContract", cellFormatLab));
			sheet.addCell(new Label(65, 0, "thuNonContract", cellFormatLab));
			sheet.addCell(new Label(66, 0, "friNonContract", cellFormatLab));
			sheet.addCell(new Label(67, 0, "satNonContract", cellFormatLab));
			sheet.addCell(new Label(68, 0, "sunNonContract", cellFormatLab));
			ArrayList<ContentValues> arrlst1 = new ArrayList<ContentValues>();
			arrlst1 = mDB.queryListByMerContracted("CONTRACTEDHOURS", strMerId,
					"noncontracted");
			for (int i = 0; i < arrlst1.size(); i++) {
				content = arrlst1.get(i);
				if (content.getAsString("userid") == null) {
					sheet.addCell(new Label(60, i + 1, content
							.getAsString("storel"), cellFormatCon));
					sheet.addCell(new Label(61, i + 1, content
							.getAsString("jobskill"), cellFormatCon));
					sheet.addCell(new Label(62, i + 1, content
							.getAsString("mon"), cellFormatCon));
					sheet.addCell(new Label(63, i + 1, content
							.getAsString("tue"), cellFormatCon));
					sheet.addCell(new Label(64, i + 1, content
							.getAsString("wed"), cellFormatCon));
					sheet.addCell(new Label(65, i + 1, content
							.getAsString("thu"), cellFormatCon));
					sheet.addCell(new Label(66, i + 1, content
							.getAsString("fri"), cellFormatCon));
					sheet.addCell(new Label(67, i + 1, content
							.getAsString("sat"), cellFormatCon));
					sheet.addCell(new Label(68, i + 1, content
							.getAsString("sun"), cellFormatCon));
				}
			}

			sheet.addCell(new Label(79, 0, "deptBranch", cellFormatLab));

			sectionA(content);

			sectionB(content);

			sectionC(content);

			sectionD(content);

			content = mDB.queryDataByMerId("MER", strMerId);
			sheet.addCell(new Label(113, 0, "NameACHolder", cellFormatLab));
			sheet.addCell(new Label(114, 0, "AccountNumber", cellFormatLab));
			sheet.addCell(new Label(115, 0, "SortCode", cellFormatLab));
			sheet.addCell(new Label(116, 0, "BuildingSocietyRefNo",
					cellFormatLab));
			content = mDB.queryDataByMerId("BANKDETAILS", strMerId);
			if (content.getAsString("userid") == null) {
				sheet.addCell(new Label(113, 1, content.getAsString("nameacc"),
						cellFormatCon));
				sheet.addCell(new Label(114, 1, content.getAsString("accnum"),
						cellFormatCon));
				sheet.addCell(new Label(115, 1,
						content.getAsString("sortcode"), cellFormatCon));
				sheet.addCell(new Label(116, 1, content.getAsString("refnum"),
						cellFormatCon));
			}

		} catch (RowsExceededException e) {
			e.printStackTrace();
		} catch (WriteException e) {
			e.printStackTrace();
		}
	}

	public void sectionA(ContentValues content) {
		try {
			content = mDB.queryDataByMerId("MER", strMerId);
			sheet.addCell(new Label(80, 0, "BackProblems", cellFormatLab));
			sheet.addCell(new Label(81, 0, "UpperLimbDisorders", cellFormatLab));
			sheet.addCell(new Label(82, 0, "AnySkinComplaints", cellFormatLab));
			sheet.addCell(new Label(83, 0, "StressRelatedDisorder",
					cellFormatLab));
			sheet.addCell(new Label(84, 0, "NumbnessTingling", cellFormatLab));
			sheet.addCell(new Label(85, 0, "LungComplaints", cellFormatLab));
			sheet.addCell(new Label(86, 0, "HeartCirculatorySystem",
					cellFormatLab));
			sheet.addCell(new Label(87, 0, "AnyDigestiveTrouble", cellFormatLab));
			sheet.addCell(new Label(88, 0,
					"AnyPsychologicalOrNervousComplaints", cellFormatLab));
			sheet.addCell(new Label(89, 0, "GoutAnyKidneyOfBladderComplaint",
					cellFormatLab));
			content = mDB.queryDataByMerAddOther("MEDICALHISTORY", strMerId,
					"sectiona");
			if (content.getAsString("userid") == null) {
				sheet.addCell(new Label(80, 1, content.getAsString("ans1"),
						cellFormatCon));
				sheet.addCell(new Label(81, 1, content.getAsString("ans2"),
						cellFormatCon));
				sheet.addCell(new Label(82, 1, content.getAsString("ans3"),
						cellFormatCon));
				sheet.addCell(new Label(83, 1, content.getAsString("ans4"),
						cellFormatCon));
				sheet.addCell(new Label(84, 1, content.getAsString("ans5"),
						cellFormatCon));
				sheet.addCell(new Label(85, 1, content.getAsString("ans6"),
						cellFormatCon));
				sheet.addCell(new Label(86, 1, content.getAsString("ans7"),
						cellFormatCon));
				sheet.addCell(new Label(87, 1, content.getAsString("ans8"),
						cellFormatCon));
				sheet.addCell(new Label(88, 1, content.getAsString("ans9"),
						cellFormatCon));
				sheet.addCell(new Label(89, 1, content.getAsString("ans10"),
						cellFormatCon));
			} else {
				sheet.addCell(new Label(80, 1, "No", cellFormatCon));
				sheet.addCell(new Label(81, 1, "No", cellFormatCon));
				sheet.addCell(new Label(82, 1, "No", cellFormatCon));
				sheet.addCell(new Label(83, 1, "No", cellFormatCon));
				sheet.addCell(new Label(84, 1, "No", cellFormatCon));
				sheet.addCell(new Label(85, 1, "No", cellFormatCon));
				sheet.addCell(new Label(86, 1, "No", cellFormatCon));
				sheet.addCell(new Label(87, 1, "No", cellFormatCon));
				sheet.addCell(new Label(88, 1, "No", cellFormatCon));
				sheet.addCell(new Label(89, 1, "No", cellFormatCon));
			}
		} catch (RowsExceededException e) {
			e.printStackTrace();
		} catch (WriteException e) {
			e.printStackTrace();
		}
	}

	public void sectionB(ContentValues content) {
		try {
			content = mDB.queryDataByMerId("MER", strMerId);
			sheet.addCell(new Label(90, 0, "FailedAMedicalExamination",
					cellFormatLab));
			sheet.addCell(new Label(91, 0, "RecommendedToConsultASpecialist",
					cellFormatLab));
			sheet.addCell(new Label(92, 0, "BeenAbsentFromWork", cellFormatLab));
			sheet.addCell(new Label(93, 0, "ConductedAClaim", cellFormatLab));
			content = mDB.queryDataByMerAddOther("MEDICALHISTORY", strMerId,
					"sectionb");
			if (content.getAsString("userid") == null) {
				sheet.addCell(new Label(90, 1, content.getAsString("ans1"),
						cellFormatCon));
				sheet.addCell(new Label(91, 1, content.getAsString("ans2"),
						cellFormatCon));
				sheet.addCell(new Label(92, 1, content.getAsString("ans3"),
						cellFormatCon));
				sheet.addCell(new Label(93, 1, content.getAsString("ans4"),
						cellFormatCon));
			} else {
				sheet.addCell(new Label(90, 1, "No", cellFormatCon));
				sheet.addCell(new Label(91, 1, "No", cellFormatCon));
				sheet.addCell(new Label(92, 1, "No", cellFormatCon));
				sheet.addCell(new Label(93, 1, "No", cellFormatCon));
			}
		} catch (RowsExceededException e) {
			e.printStackTrace();
		} catch (WriteException e) {
			e.printStackTrace();
		}
	}

	public void sectionC(ContentValues content) {
		try {
			content = mDB.queryDataByMerId("MER", strMerId);
			sheet.addCell(new Label(94, 0, "EarNoseOrThroatComplaints",
					cellFormatLab));
			sheet.addCell(new Label(95, 0, "EyeProblem", cellFormatLab));
			sheet.addCell(new Label(96, 0, "WearGlassesOrContactLenses",
					cellFormatLab));
			sheet.addCell(new Label(97, 0, "EyesLastTested", cellFormatLab));
			sheet.addCell(new Label(98, 0, "SufferFromAMedicalCondition",
					cellFormatLab));
			sheet.addCell(new Label(99, 0, "RegularMedication", cellFormatLab));
			sheet.addCell(new Label(100, 0, "AnyTreatmentFromYourDoctor",
					cellFormatLab));
			sheet.addCell(new Label(101, 0, "AbsentWithSickness", cellFormatLab));
			sheet.addCell(new Label(102, 0, "Disability", cellFormatLab));
			sheet.addCell(new Label(103, 0, "Smoke", cellFormatLab));
			sheet.addCell(new Label(104, 0, "DrinkAlcohol", cellFormatLab));
			sheet.addCell(new Label(105, 0, "Allergies", cellFormatLab));
			sheet.addCell(new Label(106, 0, "TakeRegularExercise",
					cellFormatLab));
			content = mDB.queryDataByMerAddOther("MEDICALHISTORY", strMerId,
					"sectionc");
			if (content.getAsString("userid") == null) {
				sheet.addCell(new Label(94, 1, content.getAsString("ans1"),
						cellFormatCon));
				sheet.addCell(new Label(95, 1, content.getAsString("ans2"),
						cellFormatCon));
				sheet.addCell(new Label(96, 1, content.getAsString("ans3"),
						cellFormatCon));
				sheet.addCell(new Label(97, 1, content.getAsString("ans4"),
						cellFormatCon));
				sheet.addCell(new Label(98, 1, content.getAsString("ans5"),
						cellFormatCon));
				sheet.addCell(new Label(99, 1, content.getAsString("ans6"),
						cellFormatCon));
				sheet.addCell(new Label(100, 1, content.getAsString("ans7"),
						cellFormatCon));
				sheet.addCell(new Label(101, 1, content.getAsString("ans8"),
						cellFormatCon));
				sheet.addCell(new Label(102, 1, content.getAsString("ans9"),
						cellFormatCon));
				sheet.addCell(new Label(103, 1, content.getAsString("ans10"),
						cellFormatCon));
				sheet.addCell(new Label(104, 1, content.getAsString("ans11"),
						cellFormatCon));
				sheet.addCell(new Label(105, 1, content.getAsString("ans12"),
						cellFormatCon));
				sheet.addCell(new Label(106, 1, content.getAsString("ans13"),
						cellFormatCon));
			} else {
				sheet.addCell(new Label(94, 1, "No", cellFormatCon));
				sheet.addCell(new Label(95, 1, "No", cellFormatCon));
				sheet.addCell(new Label(96, 1, "No", cellFormatCon));
				sheet.addCell(new Label(97, 1, "No", cellFormatCon));
				sheet.addCell(new Label(98, 1, "No", cellFormatCon));
				sheet.addCell(new Label(99, 1, "No", cellFormatCon));
				sheet.addCell(new Label(100, 1, "No", cellFormatCon));
				sheet.addCell(new Label(101, 1, "No", cellFormatCon));
				sheet.addCell(new Label(102, 1, "No", cellFormatCon));
				sheet.addCell(new Label(103, 1, "No", cellFormatCon));
				sheet.addCell(new Label(104, 1, "No", cellFormatCon));
				sheet.addCell(new Label(105, 1, "No", cellFormatCon));
				sheet.addCell(new Label(106, 1, "No", cellFormatCon));
			}
		} catch (RowsExceededException e) {
			e.printStackTrace();
		} catch (WriteException e) {
			e.printStackTrace();
		}
	}

	public void sectionD(ContentValues content) {
		try {
			content = mDB.queryDataByMerId("MER", strMerId);
			sheet.addCell(new Label(107, 0, "WorkedInADustyJob", cellFormatLab));
			sheet.addCell(new Label(108, 0, "WorkedInNoisyJob", cellFormatLab));
			sheet.addCell(new Label(109, 0, "AdvisedForMedicalReasons",
					cellFormatLab));
			sheet.addCell(new Label(110, 0, "WorkDeafness", cellFormatLab));
			sheet.addCell(new Label(111, 0, "Asthma", cellFormatLab));
			sheet.addCell(new Label(112, 0, "PreviouslyWorkedForDEESET",
					cellFormatLab));
			content = mDB.queryDataByMerAddOther("MEDICALHISTORY", strMerId,
					"sectiond");
			if (content.getAsString("userid") == null) {
				sheet.addCell(new Label(107, 1, content.getAsString("ans1"),
						cellFormatCon));
				sheet.addCell(new Label(108, 1, content.getAsString("ans2"),
						cellFormatCon));
				sheet.addCell(new Label(109, 1, content.getAsString("ans3"),
						cellFormatCon));
				sheet.addCell(new Label(110, 1, content.getAsString("ans4"),
						cellFormatCon));
				sheet.addCell(new Label(111, 1, content.getAsString("ans5"),
						cellFormatCon));
				sheet.addCell(new Label(112, 1, content.getAsString("ans6"),
						cellFormatCon));
			} else {
				sheet.addCell(new Label(107, 1, "No", cellFormatCon));
				sheet.addCell(new Label(108, 1, "No", cellFormatCon));
				sheet.addCell(new Label(109, 1, "No", cellFormatCon));
				sheet.addCell(new Label(110, 1, "No", cellFormatCon));
				sheet.addCell(new Label(111, 1, "No", cellFormatCon));
				sheet.addCell(new Label(112, 1, "No", cellFormatCon));
			}
		} catch (RowsExceededException e) {
			e.printStackTrace();
		} catch (WriteException e) {
			e.printStackTrace();
		}
	}

	public String convertBoolean(String strboolean) {
		if (strboolean.equals("1")) {
			return "TRUE";
		} else {
			return "FALSE";
		}
	}
}
